package com.css.fxfzaqbz.modules.base.repository.impl;


import com.css.fxfzaqbz.constants.FxfzConstants;
import com.css.fxfzaqbz.modules.base.entity.BuildingVulnerability;
import com.css.fxfzaqbz.modules.base.entity.District;
import com.css.fxfzaqbz.modules.base.queryParams.BuildingVulnerabilityQueryParams;
import com.css.fxfzaqbz.modules.base.repository.BuildingVulnerabilityRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import com.css.fxfzaqbz.zcpt.sys.service.SUserService;
import com.css.fxfzaqbz.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Arrays;
import java.util.List;

@Repository
public class BuildingVulnerabilityRepositoryImpl implements BuildingVulnerabilityRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;



    @Override
    public void save(BuildingVulnerability buildingVulnerability) {
        String sql="insert into sys_buildingvulnerability values(:id, :province, :city, :prefecture, :structureType, :intensity, :destroy, :six, :seven, " +
                ":eight, :nine, :ten, :createUser, :createTime, :updateUser, :updateTime,:isNull)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingVulnerability);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<BuildingVulnerability> getPage(BuildingVulnerability buildingVulnerability ){
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        StringBuilder orderSql = new StringBuilder(" order by structure_type,intensity,destroy ");



        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getProvince())){
            whereSql.append(" and province = '"+buildingVulnerability.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getCity())){
            whereSql.append(" and city = '"+buildingVulnerability.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getPrefecture())){
            whereSql.append(" and prefecture = '"+buildingVulnerability.getPrefecture()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getStructureType())){
            whereSql.append(" and structure_type = '"+buildingVulnerability.getStructureType()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getIntensity())){
            whereSql.append(" and intensity = '"+buildingVulnerability.getIntensity()+"'");
        }
        sql.append(whereSql).append(orderSql);

        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));

        return list;
    }

    @Override
    public void update(BuildingVulnerability buildingVulnerability) {
        String sql="update sys_buildingvulnerability set six = :six, seven= :seven ,eight = :eight ,nine = :nine " +
                ",ten = :ten ,update_user = :updateUser ,update_time = :updateTime ,is_null=:isNull where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingVulnerability);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<BuildingVulnerability> isHaveRepeatData(BuildingVulnerability buildingVulnerability) {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");



        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getProvince())){
            whereSql.append(" and province = '"+buildingVulnerability.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getCity())){
            whereSql.append(" and city = '"+buildingVulnerability.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getPrefecture())){
            whereSql.append(" and prefecture = '"+buildingVulnerability.getPrefecture()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getStructureType())){
            whereSql.append(" and structure_type = '"+buildingVulnerability.getStructureType()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getIntensity())){
            whereSql.append(" and intensity = '"+buildingVulnerability.getIntensity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(buildingVulnerability.getDestroy())){
            List<String> destroys = Arrays.asList(FxfzConstants.INTENSITY);
            whereSql.append(" and destroy in ('"+String.join("','", destroys)+"')");
        }


        sql.append(whereSql);

        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));

        return list;
    }

    @Override
    public List<BuildingVulnerability> getVulnerability(String province, String city, String area, String structureType, String sfIntensity, String dzIntensity) {
        StringBuilder sql = new StringBuilder("select structure_type,destroy,");
        if("VI".equals(dzIntensity)){
            sql.append("six");
        }else if("VII".equals(dzIntensity)){
            sql.append("seven");
        }else if("VIII".equals(dzIntensity)){
            sql.append("eight");
        }else if("IX".equals(dzIntensity)){
            sql.append("nine");
        }else if("X".equals(dzIntensity)){
            sql.append("ten");
        }
        sql.append(" as x from sys_buildingvulnerability ");
        sql.append(" where province='").append(province).append("'");
        sql.append(" and  city='").append(city).append("'");
        sql.append(" and prefecture='").append(area).append("'");
        sql.append(" and structure_type='").append(structureType).append("'");
        sql.append(" and intensity='").append(sfIntensity).append("'");
        StringBuilder orderSql = new StringBuilder(" order by destroy desc");
        List<BuildingVulnerability> list = jdbcTemplate.query(sql.append(orderSql).toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));
        return list;
    }

    @Override
    public void delete(BuildingVulnerability buildingVulnerability) {
        String sql="delete from sys_buildingvulnerability where province=:province and city=:city and prefecture =:prefecture and structure_type=:structureType";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingVulnerability);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<BuildingVulnerability> getByDistrict(District district) {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(district.getProvince())){
            whereSql.append(" and province = '"+district.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(district.getCity())){
            whereSql.append(" and city = '"+district.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(district.getPrefecture())){
            whereSql.append(" and prefecture = '"+district.getPrefecture()+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by structure_type,intensity,destroy ");
        List<BuildingVulnerability> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));
        return list;
    }

    @Override
    public List<BuildingVulnerability> getData(String province, String city, String area, String dzIntensity) {
        StringBuilder sql = new StringBuilder("select structure_type,destroy,intensity,");
        if("VI".equals(dzIntensity)){
            sql.append("six");
        }else if("VII".equals(dzIntensity)){
            sql.append("seven");
        }else if("VIII".equals(dzIntensity)){
            sql.append("eight");
        }else if("IX".equals(dzIntensity)){
            sql.append("nine");
        }else if("X".equals(dzIntensity)){
            sql.append("ten");
        }
        sql.append(" as x from sys_buildingvulnerability ");
        sql.append(" where province='").append(province).append("'");
        sql.append(" and  city='").append(city).append("'");
        sql.append(" and prefecture='").append(area).append("'");
        StringBuilder orderSql = new StringBuilder(" order by destroy desc");
        List<BuildingVulnerability> list = jdbcTemplate.query(sql.append(orderSql).toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));
        return list;
    }

}
